CREATE PROCEDURE SP_ADJUST_DISTRIBUTOR_INVENTORY
(
	@DISTRIBUTOR_ID INT,
	@PRODUCT_ID     INT,
	@LOT_ID			INT,
	@SCRAP_QTY      INT,	
	@ADDITIONAL_QTY INT
)
AS
BEGIN

	DECLARE @PROCESSSTEP_ID INT
	DECLARE @CSLIT_ID INT
	DECLARE @QTY_AVAILABLE INT

	DECLARE @TEMPCURSOR		CURSOR

	SELECT @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID
		
	SET @TEMPCURSOR = CURSOR FOR SELECT Cslit_id, lot_id, QTY_IN-ISNULl(shipped_qty,0) from Distributor_Inventory_lot where PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID 
										AND LOT_ID = @LOT_ID and QTY_IN-ISNULl(shipped_qty,0) > 0 ORDER BY DATE_IN DESC

	OPEN @TEMPCURSOR
	FETCH NEXT FROM @TEMPCURSOR INTO @CSLIT_ID, @LOT_ID, @QTY_AVAILABLE
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @SCRAP_QTY > 0
		BEGIN
			
			IF @SCRAP_QTY <= @QTY_AVAILABLE
			BEGIN
				UPDATE Distributor_Inventory_lot SET QTY_IN = QTY_IN-@SCRAP_QTY WHERE PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID AND LOT_ID = @LOT_ID AND CSLIT_ID = @CSLIT_ID
				SET @SCRAP_QTY = 0		
			END
			ELSE
			BEGIN
				UPDATE Distributor_Inventory_lot SET QTY_IN = QTY_IN-@QTY_AVAILABLE WHERE PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID AND LOT_ID = @LOT_ID AND CSLIT_ID = @CSLIT_ID
				SET @SCRAP_QTY = @SCRAP_QTY-@QTY_AVAILABLE 
			END 
						
			SET @LOT_ID 		= 0 
			SET @CSLIT_ID		= 0 

			IF @SCRAP_QTY = 0
			BEGIN
				BREAK
			END	
		END --END OF SCRAP QTY
			
		IF @ADDITIONAL_QTY > 0
		BEGIN
			UPDATE Distributor_Inventory_lot SET QTY_IN = QTY_IN+@ADDITIONAL_QTY WHERE PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID AND LOT_ID = @LOT_ID AND CSLIT_ID = @CSLIT_ID
			SET @ADDITIONAL_QTY = 0
			
			IF @ADDITIONAL_QTY = 0
			BEGIN
				BREAK
			END	
		END
					
	FETCH NEXT FROM @TEMPCURSOR INTO @CSLIT_ID, @LOT_ID, @QTY_AVAILABLE
	END
	CLOSE @TEMPCURSOR
	DEALLOCATE @TEMPCURSOR
END